﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using SistemadeEstágios.Model;

namespace SistemadeEstágios.DAL
{
    public class ContratoBD : BaseBD
    {

        public Contrato Buscar(int codigo)
        {
            Contrato obj = null;
            string query = string.Empty;

            try
            {
                query = @"Select 
                        C.dt_inicio As DtInicio, C.dt_final As DtFinal, C.dt_recisao As DtRes, C.dt_relatorio As DtRel, C.numero_contrato As Numero,
                        C.numero_apolice As Apolice, C.seguradora As Seguradora, C.valor_bolsa As Valor, C.beneficios As Bonus,
                        C.horas_diarias As HrDia, C.horas_semanais As HrSem, C.contrato As Contrato, Est.matricula As Matricula, Est.nome As EstNome,
                        Est.endereco As EstEnd, Est.cidade As EstCidade, Est.cep As EstCep, Cur.codigo As CurCod, Cur.nome As Curso, 
                        Est.semestre As Semestre, Est.telefone As EstTel, Est.email As EstMail, Est.rg As EstRG, Est.cpf As EstCPF, 
                        Med.codigo As MedCod, Med.nome As Mediadora, Emp.codigo As EmpCod, Emp.nome As Empresa, Emp.endereco As EmpEnd,
                        Emp.cidade As EmpCidade, Emp.cep As EmpCEP, Emp.cnpj As EmpCNPJ, Emp.supervisor As Supervisor, 
                        Emp.cargo_supervisor As Cargo, Emp.telefone As EmpTel, Emp.email As EmpMail, Res.codigo As ResCod,
                        Res.nome As ResNome, Res.telefone As ResTel, Res.email As ResMail, C.situacao As Sit
                    From
                        Contratos C
                            Inner Join
                        Estagiarios Est ON C.estagiario = Est.matricula
                            Inner Join
                        Curso Cur ON Est.curso = Cur.codigo
                            Inner Join
                        Empresas Emp ON C.empresa = Emp.codigo
                            Inner Join
                        Mediadora Med ON C.mediadora = Med.codigo
                            Inner Join
                        Responsaveis Res ON C.responsavel = Res.codigo Where C.codigo = @codigo;";

                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", codigo);

                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    obj = new Contrato();

                    obj.Numero = Int32.Parse(oDbManager.DBReader["Numero"].ToString());
                    obj.Apolice = Int32.Parse(oDbManager.DBReader["Apolice"].ToString());
                    obj.Seguradora = oDbManager.DBReader["Seguradora"].ToString();
                    obj.Estagiario = new Estagiaros();
                    obj.Estagiario.Matricula = Int32.Parse(oDbManager.DBReader["Matricula"].ToString());
                    obj.Estagiario.Nome = oDbManager.DBReader["EstNome"].ToString();
                    obj.Estagiario.Endereco = oDbManager.DBReader["EstEnd"].ToString();
                    obj.Estagiario.Cidade = oDbManager.DBReader["EstCidade"].ToString();
                    obj.Estagiario.CEP = oDbManager.DBReader["EstCep"].ToString();
                    obj.Estagiario.Curso = new Cursos();
                    obj.Estagiario.Curso.Codigo = Int32.Parse(oDbManager.DBReader["CurCod"].ToString());
                    obj.Estagiario.Curso.Nome = oDbManager.DBReader["Curso"].ToString();
                    obj.Estagiario.Semestre = Int32.Parse(oDbManager.DBReader["Semestre"].ToString());
                    obj.Estagiario.Telefone = oDbManager.DBReader["EstTel"].ToString();
                    obj.Estagiario.Email = oDbManager.DBReader["EstMail"].ToString();
                    obj.Estagiario.RG = oDbManager.DBReader["EstRG"].ToString();
                    obj.Estagiario.Cpf = oDbManager.DBReader["EstCPF"].ToString();
                    obj.Mediadora = new Mediadora();
                    obj.Mediadora.Codigo = Int32.Parse(oDbManager.DBReader["MedCod"].ToString());
                    obj.Mediadora.Nome = oDbManager.DBReader["Mediadora"].ToString();
                    obj.Empresa = new Empresas();
                    obj.Empresa.Codigo = Int32.Parse(oDbManager.DBReader["EmpCod"].ToString());
                    obj.Empresa.Nome = oDbManager.DBReader["Empresa"].ToString();
                    obj.Empresa.Endereco = oDbManager.DBReader["EmpEnd"].ToString();
                    obj.Empresa.Cidade = oDbManager.DBReader["EmpCidade"].ToString();
                    obj.Empresa.CEP = oDbManager.DBReader["EmpCEP"].ToString();
                    obj.Empresa.CNPJ = oDbManager.DBReader["EmpCNPJ"].ToString();
                    obj.Empresa.SupervisorNome = oDbManager.DBReader["Supervisor"].ToString();
                    obj.Empresa.SupervisorCargo = oDbManager.DBReader["Cargo"].ToString();
                    obj.Empresa.SupervisorTelefone = oDbManager.DBReader["EmpTel"].ToString();
                    obj.Empresa.SupervisorEmail = oDbManager.DBReader["EmpMail"].ToString();
                    obj.Responsavel = new Responsaveis();
                    obj.Responsavel.Codigo = Int32.Parse(oDbManager.DBReader["ResCod"].ToString());
                    obj.Responsavel.Nome = oDbManager.DBReader["ResNome"].ToString();
                    obj.Responsavel.Telefone = oDbManager.DBReader["ResTel"].ToString();
                    obj.Responsavel.Email = oDbManager.DBReader["ResMail"].ToString();
                    obj.DtInicial = DateTime.Parse(oDbManager.DBReader["DtInicio"].ToString());
                    obj.DtFinal = DateTime.Parse(oDbManager.DBReader["DtFinal"].ToString());
                    if (oDbManager.DBReader["DtRel"] == DBNull.Value)
                        obj.DtRelatorio = null;
                    else
                        obj.DtRelatorio = DateTime.Parse(oDbManager.DBReader["DtRel"].ToString());
                    if (oDbManager.DBReader["DtRes"] == DBNull.Value)
                        obj.DtRecisao = null;
                    else
                        obj.DtRecisao = DateTime.Parse(oDbManager.DBReader["DtRes"].ToString());
                    obj.Situacao = (Situacao)Enum.Parse(typeof(Situacao), oDbManager.DBReader["Sit"].ToString());
                    obj.HorasDiarias = float.Parse(oDbManager.DBReader["HrDia"].ToString());
                    obj.HorasSemanais = Int32.Parse(oDbManager.DBReader["HrSem"].ToString());
                    obj.ValorBolsa = Decimal.Parse(oDbManager.DBReader["Valor"].ToString());
                    obj.ValorBonus = Decimal.Parse(oDbManager.DBReader["Bonus"].ToString());
                    obj.Contrat = (byte[])oDbManager.DBReader["Contrato"];

                }
            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return obj;
        }

        public bool Inserir(Contrato obj)
        {
            string query = string.Empty;
            int r = -1;
            try
            {
                query = @"INSERT INTO Contratos VALUES (@codigo, @estagiario, @empresa, @mediadora,
                        @responsavel, @situacao, @dt_inicio, @dt_final, @dt_recisao, 
                        @numero_contrato, @numero_apolice, @seguradora, @valor_bolsa,
                        @beneficios, @dt_relatorio, @horas_diarias, @horas_semanais, @contrato);";

                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", obj.Numero);
                oDbManager.AddParameter("@estagiario", obj.Estagiario.Matricula);
                oDbManager.AddParameter("@empresa", obj.Empresa.Codigo);
                oDbManager.AddParameter("@mediadora", obj.Mediadora.Codigo);
                oDbManager.AddParameter("@responsavel", obj.Responsavel.Codigo);
                oDbManager.AddParameter("@situacao", (int)obj.Situacao);
                oDbManager.AddParameter("@dt_inicio", obj.DtInicial);
                oDbManager.AddParameter("@dt_final", obj.DtFinal);
                oDbManager.AddParameter("@dt_recisao", obj.DtRecisao);
                oDbManager.AddParameter("@numero_contrato", obj.Numero);
                oDbManager.AddParameter("@numero_apolice", obj.Apolice);
                oDbManager.AddParameter("@seguradora", obj.Seguradora);
                oDbManager.AddParameter("@valor_bolsa", obj.ValorBolsa);
                oDbManager.AddParameter("@beneficios", obj.ValorBonus);
                oDbManager.AddParameter("@dt_relatorio", obj.DtRelatorio);
                oDbManager.AddParameter("@horas_diarias", obj.HorasDiarias);
                oDbManager.AddParameter("@horas_semanais", obj.HorasSemanais);
                oDbManager.AddParameter("@contrato", obj.Contrat);

                r = oDbManager.ExecuteNonQuery(query, CommandType.Text);

            }
            finally
            {
                oDbManager.CloseConnection();
            }
            return (r > 0);
        }

        public byte[] BuscaModelo(int contrato)
        {
            byte[] texto = null;
            string query = string.Empty;

            try
            {
                query = "Select texto From Modelos Where codigo = @codigo";
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", contrato);

                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    texto = (byte[])oDbManager.DBReader[0];
                }
            }
            finally
            {
                oDbManager.CloseConnection();
            }
            return texto;
        }

        public bool CadastraModelo(Modelo obj)
        {
            int r = -1;
            string query = string.Empty;

            try
            {
                query = "Insert Into Modelos Values (@modelo);";
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@modelo", obj.Modelo);

                r = oDbManager.ExecuteNonQuery(query, CommandType.Text);

            }
            finally
            {
                oDbManager.CloseConnection();
            }
            return (r > 0);
        }

        public List<Atributos> BuscaAtributos()
        {
            List<Atributos> lstAtrib = null;
            Atributos atrib = null;
            string query = string.Empty;

            try
            {
                query = @"Select AR.codigo As CodigoAtributo, AR.alias_lista_atributos As NomeAtributo, 
                    AR.alias_impressao As NomeTexto From Atributos_Relatorios As AR 
                    Inner Join Atributos_Relatorios_Default As ARD On ARD.atributo_relatorio = AR.codigo";

                oDbManager.OpenConnection();

                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    lstAtrib = new List<Atributos>();
                    while (oDbManager.DBReader.Read())
                    {
                        atrib = new Atributos();
                        atrib.Codigo = Convert.ToInt32(oDbManager.DBReader["CodigoAtributo"]);
                        atrib.Atributo = oDbManager.DBReader["NomeAtributo"].ToString();
                        atrib.Texto = oDbManager.DBReader["NomeTexto"].ToString();

                        lstAtrib.Add(atrib);
                    }
                }

                return lstAtrib;
            }
            finally
            {

            }
        }
    }
}
